/*
THIS FILE IMPORTS INDIVIDUAL BOND INDEX IN USD OR LOCAL CURRENCY, AND CONVERT THEM TO A BOND INDEX IN USD 
*/
clear all
global countries "  australia belgium   brazil canada        chile    china    colombia        denmark    finland    france        germany     hongkong       india    indonesia    ireland    israel    italy    japan   malaysia    mexico   netherlands         norway  peru    philippines  poland     singapore koreasouth                   southafrica     spain    sweden    switzerland              taiwan    thailand    turkey   unitedkingdom   unitedstates  "
global nonUScountries "  australia      belgium   brazil canada        chile    china    colombia        denmark    finland    france        germany     hongkong       india    indonesia    ireland    israel    italy    japan   malaysia    mexico   netherlands         norway  peru    philippines  poland     singapore koreasouth                   southafrica     spain    sweden    switzerland              taiwan    thailand    turkey   unitedkingdom    "

* import data
import excel "$datapath/datastream/_RAWbonds.xlsx", clear sheet("clean") cellrange(A6) firstrow 
destring _all, replace force // destring variables

* nice data format
gen datew = wofd(week)
format datew %tw

gen datem = mofd(week)
format datem %tm

* keep end of month value
gen month = mofd(dofm(datem))
sort datew
gen last = (month[_n] + 1 == month[_n+1])
keep if last == 1

* drop unnecessary variables
drop datew week month last 

* rename for reshape
foreach c of global countries {
    qui rename `c' country_`c'
}
* reshape in a long format
reshape long country_ , i(datem) j(country) string
rename country_ bondindex

*****************************************************************************
* Index the variable
drop if datem > ym(2016,7)
drop if mi(bondindex)
bys country (datem) : gen index2 = 1 if _n == _N
bys country (datem) : replace index2 = bondindex/bondindex[_N] if _n < _N
* bond return
bys country (datem) : gen rbond = (index2[_n]-index2[_n-1])/index2[_n-1]
drop index2 bondindex
*****************************************************************************

*****************************************************************************
* SOME COUNTRIES HAVE THE BOND INDEX IN LOCAL CURRENCY --> CONVERT IN USD 
merge 1:1 country datem using "$datapath/Exchange_Rates_exceldata_to_DTA.dta", keep(3) nogenerate // exchange rate data

// exrate: 1 USD = x local currency

gen rbondusd = lexrate*(1+rbond)/exrate - 1 // lexrate: exchange rate end of month t-1. exrate: exchange rate end of month t

global currency "thailand switzerland spain	poland norway india"
foreach c of global currency {
    qui replace rbond = rbondusd if country == "`c'"
}
*****************************************************************************

*****************************************************************************
* FINAL INDEX
drop if mi(rbond)
bys country (datem) : gen bondindex = 1 if _n == _N
forvalues j = 1(1)400 { // do it recursively for 400 months
	bys country (datem) : replace bondindex = bondindex[_n+1]/(1+rbond[_n+1]) if _n < _N
}

keep datem country bondindex

*****************************************************************************

* save
save "$datapath/bonds.dta", replace
* end
*****************************************************************************